ESD - Document Database

This is about a system for creating and managing medical transcriptions which was put into place in 1993. It was completely re-written in 2007–native OS X application with modern look-and-feel, currently using MS Word 2004 & 2011 for the word processing component–but I haven't had time to completely re-do this section of the web site.

The database integration & template creation, which are the essential features of this system, are much the same. If you're interested, try to imagine this with a modern user interface...

Database Integration
Template Creation
Template Versatility
User Extensibility
The Major Restriction
What's Not So Good
The Future

Database Integration

The documents are truly integrated into the application. Documents are not saved into a special directory then "checked in" to the database by the user, they are created within the program and automatically (without error) connected to the appropriate records in the database.

Here is the dialog for creating a new document:

The patient name lookup field triggers a very sophisticated search (phonetic matching, partial matching, insensitive to name order, and relevance-ranked). The other basic data (creator, signer, state, template, date, clinical setting, description) apply to all documents. Below that are a set of popups for choosing related records. For any table referenced by a template, the popup lets the user choose the appropriate record--although they default to the latest and so the users basically never use the popups. (There is a slightly simpler dialog for non-patient correspondence which I'll not discuss, and a more complicated one for entering user-defiend data which I'll describe later.)

In the case of this template, clicking the create button generates this document:

You can see that the patient name & dob, the appointment date and time, the doctor name, patient address, doctor/secretary initials were all pulled into the document from the database. (You may notice there was no appointment popup on the creation dialog even though the template pulls in appointment data. We've had some back and forth about whether a user should be able to select an old appointment or not, so the user interface in this case is not quite perfectly synch'd with the software's functionality.)

Clicking the enter button saves this document into the database and associates it with the patient record. Here is the primary patient info form, showing the patient's documents:

Documents can be easily searched by patient, author, transcriptionist, date, template, description, or the full text of the document. Given a list of documents returned by a search, a simple menu choice will pull up the list of corresponding patients (or referring physicians, or several other related tables).

Template Creation

Templates are easy to create, easy to read, and integrated into the database just like documents. Here is the template that was used (appointment) to create the document in the previous image:

The data references are shown enclosed by << >> characters. You create a template exactly as you would a document in a word processor, except for the addition of a menu item to insert data references. The data references available for insertion are grouped into logical categories (which I called data sets) to make them easier to find:

There are two types of templates: "master" which are intended to become whole documents, and non-master which are intended to be included in other templates or in documents. That's what the templates category is. After templates come the database tables available for inclusion, and after those come user-defined data items which I'll talk more about further on. Here are the items in the basic category, which is a special category that includes the most commonly used items no matter what tables they come from:

Some of the basic data items also have some programming intelligence behind them. Patient age below a certain threshold is expressed in months instead of years. You can see that there are items which cover the full complement of pronouns and adapt to the patient gender. The address items format correctly and don't put in blank address lines.

Template Versatility

I already mentioned master and non-master documents. Holding down shift while choosing the print menu item causes the selected text to be printed on an envelope along with the letter. (And many templates are set up to automatically select the patient or referring physician address when the document is created.) Holding down option while choosing print brings up a fax dialog.Any template can be designated as "fax with letterhead" which means that when faxed any document based on it will be imaged onto a scanned picture of the letterhead so that it looks like it would if you faxed the actual printed document. When creating a template, you can choose to base it on any existing template. Any reference that you can insert into a template, you can also insert into a document while editing it, that includes inserting non-master templates.

Here is the template creation dialog:

The "CC" button on the document form brings up a dialog which allows you to select additional doctors for which to print envelopes. (That really has nothing to do with templates, but it is one more example of the tight integration between the documents and the database.)

User Extensibility

Here is the dialog for defining a custom data reference:

I'll give just a brief explanation: category is for grouping them in the data reference insertion dialog, name is the reference description, data type allows some rudimentary input checking, a reference can be required or can be allowed to have no value, the description is help text for display in the insertion dialog, a reference can have a list of choices displayed as a popup during entry with a name to display and a possibly different value to insert (because the text to be inserted may be too long to display in a popup), and a reference with a choice list can have optionally allow multiple values. It's not necessary to spend time on every detail here, it'll be clearer when seen in use.

Here is a complex template using a number of user-defined data items:

Note all the references embedded. Here is the document creation dialog for this template, with a list of all the user-defined data references, and a choice being made for one:

Here is the dialog with all the data items filled in:

Except for the numbers they nearly all have popup choice menus in this case. (The values are just for illustration and don't necessarily make medical sense.)

One note: the popup of other doctors is not populated because there are none entered for the example patient. This explains the odd wording you'll see in the first sentence of the document where the referring physician name is missing.

Here is the result of clicking the create button, without any editing at all:

If you skim it, you'll see how the data references are used to build a complex document with only a bare minimum of typing.

The user-defined data capability is currently a work in progress, and is not being used yet because the client has some workflow/delegation issues to get straightened out first. The intent is that for some of the documents there will be no secretarial staff involved at all; the doctor will fill in the data and generate the document himself without going through any dictate/review/correct/review cycle.

There is always a tradeoff here. Having me add tables to the database results in better-structured data and allows: better input validation, more tailored forms rather than just an input list, better searching and reporting. Having users add their own items allows new types of data to be incorporated into templates very quickly and without additional development cost. It's going to take some experience to figure out how to properly evaluate those decisions.

The Major Restriction

The template-building facility is based entirely on a fill-in-the-blank model. There is no ability to specify an if-this-then-that-else-other logic within a template. We've talked about this off and on over the years, but never seriously because there were always things that were much more important. My biggest problem in thinking about this is that I don't know how much of a language would be appropriate; I think some experimentation would be required.

What's Not So Good

The word processor in use here is 4D Write. In 1993 its ability to be integrated into a database this way was absolutely unique. That ability was (and still is) important enough to outweigh some serious drawbacks. Overall 4D Write is slightly less powerful than an old version of MacWrite.

4D Write had no spell-checker; I wrote one but 4D Write's ability to integrate its functions was limited in some ways, so the spell-checking function is slow and clumsy--and the users' number one complaint. Until 2000 it was impossible to use superscripts in 4D Write and get even line spacing; now it is merely difficult. Tables in 4D Write are beyond useless; they're like some kind of twisted practical joke on the user. (When you finish with the create table dialog the table becomes a picture and you can't edit it; any changes require you to recreate it.)

The Future

With Office 98 Microsoft brought Visual BASIC for Applications to the Mac versions of their products. I believe that it is now possible to customize Microsoft Word sufficiently to get a similar level of integration with the database. It's possible that using Word might involve some minor compromises on the integration, but only such minor ones that I'm sure that having the MS Word level of word processing power would more than be worth it. (And only on the Mac; with OLE/ActiveX on Windows I don't think the user interface would suffer any degradation from switching to MS Word.)

I've recently added a user-defined glossary, and an auto-text feature whose predictions are based on a sophisticated analysis of the actual text (by physician and document type) of documents in the database. We are beginning to evaluate voice recognition, and it looks like this time around it might be good enough to put into production.